﻿using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Newtonsoft.Json;

namespace DAL
{
    public class DBHelper
    {
        string constr;
        public DBHelper(IConfiguration configuration)
        {
            constr = configuration.GetConnectionString("Db");
        }
        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sql">待执行的sql</param>
        /// <returns>返回受影响的行数</returns>
        public int NonQuery(string sql)
        {
            using (SqlConnection conn=new SqlConnection(constr))
            {
                conn.Open();
                SqlCommand cmd=new SqlCommand(sql, conn);   
                return cmd.ExecuteNonQuery();   
            }
        }
        /// <summary>
        /// 查询返回首行首列
        /// </summary>
        /// <param name="sql">待执行的sql</param>
        /// <returns>返回首行首列</returns>
        public object Scalar(string sql)
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                return cmd.ExecuteScalar();
            }
        }
        /// <summary>
        /// 查询返回泛型集合
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="sql">待执行的sql</param>
        /// <returns>泛型集合</returns>
        public List<T> GetList<T>(string sql)
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {
                SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
                DataTable dt=new DataTable();
                sda.Fill(dt);
                string json=JsonConvert.SerializeObject(dt);
                List<T> list=JsonConvert.DeserializeObject<List<T>>(json);
                return list;        
            }
        }
        /// <summary>
        /// 批量方法
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="ids"></param>   
        /// <param name="ziduan"></param>
        /// <returns></returns>
        public int BatchDeleteByIds(string tableName, int[] ids,string ziduan)
        {
            if (ids.Length == 0) return 0;
            string idList = string.Join(",", ids);
            string sql = $"DELETE FROM {tableName} WHERE {ziduan} IN ({idList})";
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    return cmd.ExecuteNonQuery();
                }
            }
        }
    }
}
